﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using System.Net.Sockets;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using NewLife;
using NewLife.Collections;
using NewLife.Data;
using NewLife.Log;
using NewLife.Reflection;
using XCode.Exceptions;

namespace XCode.DataAccessLayer;

/// <summary>数据库会话基类</summary>
internal abstract partial class DbSession : DisposeBase, IDbSession
{
    #region 构造函数
    protected DbSession(IDatabase db)
    {
        Database = db;
        ShowSQL = db.ShowSQL;
    }

    /// <summary>销毁资源时，回滚未提交事务，并关闭数据库连接</summary>
    /// <param name="disposing"></param>
    protected override void Dispose(Boolean disposing)
    {
        base.Dispose(disposing);

        Transaction.TryDispose();
        Transaction = null;
    }
    #endregion

    #region 属性
    /// <summary>数据库</summary>
    public IDatabase Database { get; }
    #endregion

    #region 打开/关闭
    /// <summary>当异常发生时触发。关闭数据库连接，或者返还连接到连接池。</summary>
    /// <param name="ex"></param>
    /// <returns></returns>
    protected virtual XDbException OnException(Exception ex)
    {
        if (ex != null)
            return new XDbSessionException(this, ex);
        else
            return new XDbSessionException(this);
    }

    /// <summary>当异常发生时触发。关闭数据库连接，或者返还连接到连接池。</summary>
    /// <param name="ex"></param>
    /// <param name="cmd"></param>
    /// <param name="sql"></param>
    /// <returns></returns>
    protected virtual XSqlException OnException(Exception ex, DbCommand cmd, String sql)
    {
        if (sql.IsNullOrEmpty()) sql = GetSql(cmd);
        if (ex != null)
            return new XSqlException(sql, this, ex);
        else
            return new XSqlException(sql, this);
    }

    /// <summary>打开连接并执行操作</summary>
    /// <typeparam name="TResult"></typeparam>
    /// <param name="callback"></param>
    /// <returns></returns>
    public virtual TResult Process<TResult>(Func<DbConnection, TResult> callback)
    {
        var delay = 1000;
        var retry = Database.RetryOnFailure;
        for (var i = 0; i <= retry; i++)
        {
            try
            {
                using var conn = Database.OpenConnection();
                return callback(conn);
            }
            catch (Exception ex)
            {
                // 如果重试次数用完，或者不应该在该异常上重试，则直接向上抛出异常
                if (i == retry || !ShouldRetryOn(ex)) throw;

                if (XTrace.Log.Level <= LogLevel.Debug) WriteLog("retry {0}，delay {1}", i + 1, delay);
                Thread.Sleep(delay);

                // 间隔时间倍增，最大30秒
                delay *= 2;
                if (delay > 30_000) delay = 30_000;
            }
        }

        return default;
    }

    /// <summary>打开连接并执行操作</summary>
    /// <typeparam name="TResult"></typeparam>
    /// <param name="callback"></param>
    /// <returns></returns>
    public virtual TResult Process<TResult>(Func<TResult> callback)
    {
        var delay = 1000;
        var retry = Database.RetryOnFailure;
        for (var i = 0; i <= retry; i++)
        {
            try
            {
                return callback();
            }
            catch (Exception ex)
            {
                // 如果重试次数用完，或者不应该在该异常上重试，则直接向上抛出异常
                if (i == retry || !ShouldRetryOn(ex)) throw;

                if (XTrace.Log.Level <= LogLevel.Debug) WriteLog("retry {0}，delay {1}", i + 1, delay);
                Thread.Sleep(delay);

                // 间隔时间倍增，最大30秒
                delay *= 2;
                if (delay > 30_000) delay = 30_000;
            }
        }

        return default;
    }

    /// <summary>打开连接并执行操作</summary>
    /// <typeparam name="TResult"></typeparam>
    /// <param name="callback"></param>
    /// <returns></returns>
    public virtual async Task<TResult> ProcessAsync<TResult>(Func<DbConnection, Task<TResult>> callback)
    {
        var delay = 1000;
        var retry = Database.RetryOnFailure;
        for (var i = 0; i <= retry; i++)
        {
            try
            {
                using var conn = await Database.OpenConnectionAsync();
                return await callback(conn);
            }
            catch (Exception ex)
            {
                // 如果重试次数用完，或者不应该在该异常上重试，则直接向上抛出异常
                if (i == retry || !ShouldRetryOn(ex)) throw;

                if (XTrace.Log.Level <= LogLevel.Debug) WriteLog("retry {0}，delay {1}", i + 1, delay);
                Thread.Sleep(delay);

                // 间隔时间倍增，最大30秒
                delay *= 2;
                if (delay > 30_000) delay = 30_000;
            }
        }

        return default;
    }

    /// <summary>打开连接并执行操作</summary>
    /// <typeparam name="TResult"></typeparam>
    /// <param name="callback"></param>
    /// <returns></returns>
    public virtual async Task<TResult> ProcessAsync<TResult>(Func<Task<TResult>> callback)
    {
        var delay = 1000;
        var retry = Database.RetryOnFailure;
        for (var i = 0; i <= retry; i++)
        {
            try
            {
                return await callback();
            }
            catch (Exception ex)
            {
                // 如果重试次数用完，或者不应该在该异常上重试，则直接向上抛出异常
                if (i == retry || !ShouldRetryOn(ex)) throw;

                if (XTrace.Log.Level <= LogLevel.Debug) WriteLog("retry {0}，delay {1}", i + 1, delay);
                Thread.Sleep(delay);

                // 间隔时间倍增，最大30秒
                delay *= 2;
                if (delay > 30_000) delay = 30_000;
            }
        }

        return default;
    }

    /// <summary>是否应该在该异常上重试</summary>
    /// <param name="ex"></param>
    /// <returns></returns>
    protected virtual Boolean ShouldRetryOn(Exception ex)
    {
        if (ex == null) return false;

        // 基础异常
        if (ex is TimeoutException) return true;
        if (ex is SocketException sex)
        {
            switch (sex.SocketErrorCode)
            {
                case SocketError.ConnectionAborted:
                case SocketError.ConnectionReset:
                case SocketError.ConnectionRefused:
                    return true;
            }
        }

        // 叠加异常
        if (ex is AggregateException agg)
        {
            foreach (var item in agg.InnerExceptions)
            {
                if (ShouldRetryOn(item)) return true;
            }
        }

        // 内部异常
        var inner = ex.InnerException;
        if (inner != null && ShouldRetryOn(inner)) return true;

        return false;
    }
    #endregion

    #region 事务
    /// <summary>数据库事务。不是线程安全，不支持多线程共用</summary>
    public ITransaction Transaction { get; private set; }

    /// <summary>开始事务</summary>
    /// <remarks>
    /// Read Uncommitted: 允许读取脏数据，一个事务能看到另一个事务还没有提交的数据。（不会阻止其它操作）
    /// Read Committed: 确保事务读取的数据都必须是已经提交的数据。它限制了读取中间的，没有提交的，脏的数据。
    /// 但是它不能确保当事务重新去读取的时候，读的数据跟上次读的数据是一样的，也就是说当事务第一次读取完数据后，
    /// 该数据是可能被其他事务修改的，当它再去读取的时候，数据可能是不一样的。（数据隐藏，不阻止）
    /// Repeatable Read: 是一个更高级别的隔离级别，如果事务再去读取同样的数据，先前的数据是没有被修改过的。（阻止其它修改）
    /// Serializable: 它做出了最有力的保证，除了每次读取的数据是一样的，它还确保每次读取没有新的数据。（阻止其它添删改）
    /// </remarks>
    /// <param name="level">事务隔离等级</param>
    /// <returns>剩下的事务计数</returns>
    public virtual Int32 BeginTransaction(IsolationLevel level)
    {
        if (Disposed) throw new ObjectDisposedException(GetType().Name);

        try
        {
            var tr = Transaction;
            if (tr == null || tr is DisposeBase db && db.Disposed)
            {
                tr = new Transaction(this, level);

                Transaction = tr;
            }

            return tr.Begin().Count;
        }
        catch (DbException ex)
        {
            throw OnException(ex);
        }
    }

    /// <summary>提交事务</summary>
    /// <returns>剩下的事务计数</returns>
    public virtual Int32 Commit()
    {
        var tr = Transaction;
        if (tr == null) throw new XDbSessionException(this, "当前并未开始事务，请用BeginTransaction方法开始新事务！");

        try
        {
            tr.Commit();
        }
        catch (DbException ex)
        {
            throw OnException(ex);
        }
        finally
        {
            if (tr.Count == 0) Transaction = null;
        }

        return tr.Count;
    }

    /// <summary>回滚事务</summary>
    /// <param name="ignoreException">是否忽略异常</param>
    /// <returns>剩下的事务计数</returns>
    public Int32 Rollback(Boolean ignoreException = true)
    {
        var tr = Transaction;
        if (tr == null) throw new XDbSessionException(this, "当前并未开始事务，请用BeginTransaction方法开始新事务！");

        try
        {
            tr.Rollback();
        }
        catch (DbException ex)
        {
            if (!ignoreException) throw OnException(ex);
        }
        finally
        {
            if (tr.Count == 0) Transaction = null;
        }

        return tr.Count;
    }
    #endregion

    #region 基本方法 查询/执行
    /// <summary>执行SQL查询，返回记录集</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public virtual DataSet Query(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
    {
        using var cmd = OnCreateCommand(sql, type, ps);
        return Query(cmd);
    }

    /// <summary>执行DbCommand，返回记录集</summary>
    /// <param name="cmd">DbCommand</param>
    /// <returns></returns>
    public virtual DataSet Query(DbCommand cmd)
    {
        return Execute(cmd, true, cmd2 =>
        {
            using var da = Database.Factory.CreateDataAdapter();
            da.SelectCommand = cmd2;

            var ds = new DataSet();
            da.Fill(ds);

            return ds;
        });
    }

    /// <summary>执行SQL查询，返回记录集</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public virtual DbTable Query(String sql, IDataParameter[] ps)
    {
        using var cmd = OnCreateCommand(sql, CommandType.Text, ps);
        return Execute(cmd, true, cmd2 =>
        {
            using var dr = cmd2.ExecuteReader();
            return OnFill(dr);
        });
    }

    protected virtual DbTable OnFill(DbDataReader dr)
    {
        var dt = new DbTable();
        dt.Read(dr);
        return dt;
    }

    private static readonly Regex reg_QueryCount = new(@"^\s*select\s+\*\s+from\s+([\w\W]+)\s*$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
    /// <summary>执行SQL查询，返回总记录数</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public virtual Int64 QueryCount(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
    {
        if (sql.Contains(" "))
        {
            _ = DbBase.CheckOrderClause(ref sql);
            var ms = reg_QueryCount.Matches(sql);
            if (ms != null && ms.Count > 0)
                sql = $"Select Count(*) From {ms[0].Groups[1].Value}";
            else
                sql = $"Select Count(*) From {DbBase.CheckSimpleSQL(sql)}";
        }
        else
            sql = $"Select Count(*) From {Database.FormatName(sql)}";

        return ExecuteScalar<Int64>(sql, type, ps);
    }

    /// <summary>执行SQL查询，返回总记录数</summary>
    /// <param name="builder">查询生成器</param>
    /// <returns>总记录数</returns>
    public virtual Int64 QueryCount(SelectBuilder builder) => ExecuteScalar<Int64>(builder.SelectCount().ToString(), CommandType.Text, builder.Parameters.ToArray());

    /// <summary>快速查询单表记录数，稍有偏差</summary>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public virtual Int64 QueryCountFast(String tableName) => QueryCount(tableName);

    /// <summary>执行SQL语句，返回受影响的行数</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public virtual Int32 Execute(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
    {
        using var cmd = OnCreateCommand(sql, type, ps);
        return Execute(cmd, false, static cmd2 => cmd2.ExecuteNonQuery());
    }

    /// <summary>执行DbCommand，返回受影响的行数</summary>
    /// <param name="cmd">DbCommand</param>
    /// <returns></returns>
    public virtual Int32 Execute(DbCommand cmd) => Execute(cmd, false, static cmd2 => cmd2.ExecuteNonQuery());

    public virtual T Execute<T>(DbCommand cmd, Boolean query, Func<DbCommand, T> callback)
    {
        Transaction?.Check(cmd, !query);

        var text = WriteSQL(cmd);

        // 外部或事务中，可能已经设置了连接
        if (cmd.Connection != null)
        {
            return Process(() =>
            {
                try
                {
                    BeginTrace();
                    return callback(cmd);
                }
                catch (DbException ex)
                {
                    throw OnException(ex, cmd, text);
                }
                finally
                {
                    EndTrace(cmd, text);
                }
            });
        }
        else
        {
            return Process(conn =>
            {
                try
                {
                    cmd.Connection = conn;

                    BeginTrace();
                    return callback(cmd);
                }
                catch (DbException ex)
                {
                    throw OnException(ex, cmd, text);
                }
                finally
                {
                    EndTrace(cmd, text);
                }
            });
        }
    }

    /// <summary>执行插入语句并返回新增行的自动编号</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns>新增行的自动编号</returns>
    public virtual Int64 InsertAndGetIdentity(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
    {
        using var cmd = OnCreateCommand(sql, type, ps);

        return Execute(cmd, false, static cmd2 =>
        {
            var rs = cmd2.ExecuteScalar();
            if (rs == null || rs == DBNull.Value) return 0;

            return Reflect.ChangeType<Int64>(rs);
        });
    }

    /// <summary>执行SQL语句，返回结果中的第一行第一列</summary>
    /// <typeparam name="T">返回类型</typeparam>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public virtual T ExecuteScalar<T>(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
    {
        using var cmd = OnCreateCommand(sql, type, ps);
        return Execute(cmd, true, static cmd2 =>
        {
            var rs = cmd2.ExecuteScalar();
            if (rs == null || rs == DBNull.Value) return default;
            if (rs is T t) return t;

            return (T)Reflect.ChangeType(rs, typeof(T));
        });
    }

    /// <summary>获取一个DbCommand。</summary>
    /// <remark>
    /// 配置了连接，并关联了事务。
    /// 连接已打开。
    /// </remark>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public virtual DbCommand CreateCommand(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
    {
        var cmd = OnCreateCommand(sql, type, ps);
        Transaction?.Check(cmd, true);

        return cmd;
    }

    /// <summary>获取一个DbCommand。</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    protected virtual DbCommand OnCreateCommand(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
    {
        var cmd = Database.Factory?.CreateCommand();
        if (cmd == null) return null;

        cmd.CommandType = type;
        cmd.CommandText = sql;
        if (ps != null && ps.Length > 0) cmd.Parameters.AddRange(ps);

        var timeout = Database.CommandTimeout;
        if (timeout <= 0) timeout = Setting.Current.CommandTimeout;
        if (timeout > 0) cmd.CommandTimeout = timeout;

        return cmd;
    }
    #endregion

    #region 异步操作
    /// <summary>执行SQL查询，返回记录集</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public virtual Task<DbTable> QueryAsync(String sql, IDataParameter[] ps)
    {
        using var cmd = OnCreateCommand(sql, CommandType.Text, ps);
        return ExecuteAsync(cmd, true, async cmd2 =>
        {
            using var dr = await cmd2.ExecuteReaderAsync();
            var dt = new DbTable();
            await dt.ReadAsync(dr);
            return dt;
        });
    }

    /// <summary>执行SQL查询，返回总记录数</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public virtual Task<Int64> QueryCountAsync(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
    {
        if (sql.Contains(" "))
        {
            _ = DbBase.CheckOrderClause(ref sql);
            var ms = reg_QueryCount.Matches(sql);
            if (ms != null && ms.Count > 0)
                sql = $"Select Count(*) From {ms[0].Groups[1].Value}";
            else
                sql = $"Select Count(*) From {DbBase.CheckSimpleSQL(sql)}";
        }
        else
            sql = $"Select Count(*) From {Database.FormatName(sql)}";

        return ExecuteScalarAsync<Int64>(sql, type, ps);
    }

    /// <summary>执行SQL查询，返回总记录数</summary>
    /// <param name="builder">查询生成器</param>
    /// <returns>总记录数</returns>
    public virtual Task<Int64> QueryCountAsync(SelectBuilder builder) => ExecuteScalarAsync<Int64>(builder.SelectCount().ToString(), CommandType.Text, builder.Parameters.ToArray());

    /// <summary>快速查询单表记录数，稍有偏差</summary>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public virtual Task<Int64> QueryCountFastAsync(String tableName) => QueryCountAsync(tableName);

    /// <summary>执行SQL语句，返回受影响的行数</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public virtual Task<Int32> ExecuteAsync(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
    {
        using var cmd = OnCreateCommand(sql, type, ps);
        return ExecuteAsync(cmd, false, cmd2 => cmd2.ExecuteNonQueryAsync());
    }

    /// <summary>执行DbCommand，返回受影响的行数</summary>
    /// <param name="cmd">DbCommand</param>
    /// <returns></returns>
    public virtual Task<Int32> ExecuteAsync(DbCommand cmd) => ExecuteAsync(cmd, false, cmd2 => cmd2.ExecuteNonQueryAsync());

    /// <summary>执行插入语句并返回新增行的自动编号</summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns>新增行的自动编号</returns>
    public virtual Task<Int64> InsertAndGetIdentityAsync(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
    {
        using var cmd = OnCreateCommand(sql, type, ps);

        return ExecuteAsync(cmd, false, async cmd2 =>
        {
            var rs = await cmd.ExecuteScalarAsync();
            if (rs == null || rs == DBNull.Value) return 0;

            return Reflect.ChangeType<Int64>(rs);
        });
    }

    /// <summary>执行SQL语句，返回结果中的第一行第一列</summary>
    /// <typeparam name="T">返回类型</typeparam>
    /// <param name="sql">SQL语句</param>
    /// <param name="type">命令类型，默认SQL文本</param>
    /// <param name="ps">命令参数</param>
    /// <returns></returns>
    public virtual Task<T> ExecuteScalarAsync<T>(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
    {
        using var cmd = OnCreateCommand(sql, type, ps);
        return ExecuteAsync(cmd, true, async cmd2 =>
        {
            var rs = await cmd.ExecuteScalarAsync();
            if (rs == null || rs == DBNull.Value) return default;
            if (rs is T t) return t;

            return (T)Reflect.ChangeType(rs, typeof(T));
        });
    }

    public virtual Task<T> ExecuteAsync<T>(DbCommand cmd, Boolean query, Func<DbCommand, Task<T>> callback)
    {
        Transaction?.Check(cmd, !query);

        var text = WriteSQL(cmd);

        // 外部或事务中，可能已经设置了连接
        if (cmd.Connection != null)
        {
            return ProcessAsync(() =>
            {
                try
                {
                    BeginTrace();
                    return callback(cmd);
                }
                catch (DbException ex)
                {
                    throw OnException(ex, cmd, text);
                }
                finally
                {
                    EndTrace(cmd, text);
                }
            });
        }
        else
        {
            return ProcessAsync(conn =>
            {
                try
                {
                    cmd.Connection = conn;

                    BeginTrace();
                    return callback(cmd);
                }
                catch (DbException ex)
                {
                    throw OnException(ex, cmd, text);
                }
                finally
                {
                    EndTrace(cmd, text);
                }
            });
        }
    }
    #endregion

    #region 批量操作
    /// <summary>批量插入</summary>
    /// <param name="table">数据表</param>
    /// <param name="columns">要插入的字段，默认所有字段</param>
    /// <param name="list">实体列表</param>
    /// <returns></returns>
    public virtual Int32 Insert(IDataTable table, IDataColumn[] columns, IEnumerable<IExtend> list) => throw new NotSupportedException();

    /// <summary>批量忽略插入</summary>
    /// <param name="table">数据表</param>
    /// <param name="columns">要插入的字段，默认所有字段</param>
    /// <param name="list">实体列表</param>
    /// <returns></returns>
    public virtual Int32 InsertIgnore(IDataTable table, IDataColumn[] columns, IEnumerable<IExtend> list) => throw new NotSupportedException();

    /// <summary>批量替换</summary>
    /// <param name="table">数据表</param>
    /// <param name="columns">要插入的字段，默认所有字段</param>
    /// <param name="list">实体列表</param>
    /// <returns></returns>
    public virtual Int32 Replace(IDataTable table, IDataColumn[] columns, IEnumerable<IExtend> list) => throw new NotSupportedException();

    /// <summary>批量更新</summary>
    /// <param name="table">数据表</param>
    /// <param name="columns">要更新的字段，默认所有字段</param>
    /// <param name="updateColumns">要更新的字段，默认脏数据</param>
    /// <param name="addColumns">要累加更新的字段，默认累加</param>
    /// <param name="list">实体列表</param>
    /// <returns></returns>
    public virtual Int32 Update(IDataTable table, IDataColumn[] columns, ICollection<String> updateColumns, ICollection<String> addColumns, IEnumerable<IExtend> list) => throw new NotSupportedException();

    /// <summary>批量插入或更新</summary>
    /// <param name="table">数据表</param>
    /// <param name="columns">要插入的字段，默认所有字段</param>
    /// <param name="updateColumns">主键已存在时，要更新的字段</param>
    /// <param name="addColumns">主键已存在时，要累加更新的字段</param>
    /// <param name="list">实体列表</param>
    /// <returns></returns>
    public virtual Int32 Upsert(IDataTable table, IDataColumn[] columns, ICollection<String> updateColumns, ICollection<String> addColumns, IEnumerable<IExtend> list) => throw new NotSupportedException();

    protected virtual void BuildInsert(StringBuilder sb, DbBase db, String action, IDataTable table, IDataColumn[] columns)
    {
        // 字段列表
        sb.AppendFormat("{0} {1}(", action, db.FormatName(table));
        foreach (var dc in columns)
        {
            sb.Append(db.FormatName(dc));
            sb.Append(',');
        }
        sb.Length--;
        sb.Append(')');
    }

    protected virtual void BuildBatchValues(StringBuilder sb, DbBase db, String action, IDataTable table, IDataColumn[] columns, IEnumerable<IExtend> list)
    {
        // 优化支持DbTable
        if (list.FirstOrDefault() is DbRow)
        {
            // 提前把列名转为索引，然后根据索引找数据。外部确保数据列在数据源中都存在
            DbTable dt = null;
            Int32[] ids = null;
            foreach (DbRow dr in list)
            {
                if (dr.Table != dt)
                {
                    dt = dr.Table;
                    var cs = new List<Int32>();
                    foreach (var dc in columns)
                    {
                        var idx = dt.GetColumn(dc.Name);
                        if (idx < 0) idx = dt.GetColumn(dc.ColumnName);
                        if (idx < 0) throw new XCodeException($"DbTable中不存在名为[{dc.Name}/{dc.ColumnName}]的字段");

                        cs.Add(idx);
                    }
                    ids = cs.ToArray();
                }

                sb.Append('(');
                var row = dt.Rows[dr.Index];
                for (var i = 0; i < columns.Length; i++)
                {
                    sb.Append(db.FormatValue(columns[i], row[ids[i]]));
                    sb.Append(',');
                }
                sb.Length--;
                sb.Append("),");
            }
        }
        else
        {
            foreach (var entity in list)
            {
                sb.Append('(');
                foreach (var dc in columns)
                {
                    sb.Append(db.FormatValue(dc, entity[dc.Name]));
                    sb.Append(',');
                }
                sb.Length--;
                sb.Append("),");
            }
        }
        sb.Length--;
    }

    protected virtual void BuildDuplicateKey(StringBuilder sb, DbBase db, IDataColumn[] columns, ICollection<String> updateColumns, ICollection<String> addColumns)
    {
        // 重复键执行update
        if ((updateColumns != null && updateColumns.Count > 0) || (addColumns != null && addColumns.Count > 0))
        {
            sb.Append(" On Duplicate Key Update ");
            if (updateColumns != null && updateColumns.Count > 0)
            {
                foreach (var dc in columns)
                {
                    if (dc.Identity || dc.PrimaryKey) continue;

                    if (updateColumns.Contains(dc.Name) && (addColumns == null || !addColumns.Contains(dc.Name)))
                        sb.AppendFormat("{0}=Values({0}),", db.FormatName(dc));
                }
                sb.Length--;
            }
            if (addColumns != null && addColumns.Count > 0)
            {
                sb.Append(',');
                foreach (var dc in columns)
                {
                    if (dc.Identity || dc.PrimaryKey) continue;

                    if (addColumns.Contains(dc.Name))
                        sb.AppendFormat("{0}={0}+Values({0}),", db.FormatName(dc));
                }
                sb.Length--;
            }
        }
    }
    #endregion

    #region 高级
    /// <summary>清空数据表，标识归零</summary>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public virtual Int32 Truncate(String tableName) => Execute($"Truncate Table {Database.FormatName(tableName)}");
    #endregion

    #region 架构
    /// <summary>返回数据源的架构信息。缓存10分钟</summary>
    /// <param name="conn">连接</param>
    /// <param name="collectionName">指定要返回的架构的名称。</param>
    /// <param name="restrictionValues">为请求的架构指定一组限制值。</param>
    /// <returns></returns>
    public virtual DataTable GetSchema(DbConnection conn, String collectionName, String[] restrictionValues)
    {
        // 小心collectionName为空，此时列出所有架构名称
        var key = "" + collectionName;
        if (restrictionValues != null && restrictionValues.Length > 0) key += "_" + String.Join("_", restrictionValues);

        var db = Database as DbBase;
        DataTable dt;
        //var dt = db._SchemaCache.Get<DataTable>(key);
        //if (dt == null)
        {
            /*
            * TODO: Bug
            * sqlserver切换到master库时,仍然使用Process去获取DbConnection，然而此时DataBase对象为连接字符串中的数据库
            * 这里不知道是应该在RemoteDb的OpenDatabase方法（改变DataBase对象）抑或是修改这里的Process方法
            */
            if (conn != null)
                dt = GetSchemaInternal(conn, key, collectionName, restrictionValues);
            else
                dt = Process(conn2 => GetSchemaInternal(conn2, key, collectionName, restrictionValues));

            //db._SchemaCache.Set(key, dt, 10);
        }

        return dt;
    }

    private DataTable GetSchemaInternal(DbConnection conn, String key, String collectionName, String[] restrictionValues)
    {
        DataTable dt = null;

        var sw = Stopwatch.StartNew();

        if (restrictionValues == null || restrictionValues.Length <= 0)
        {
            if (String.IsNullOrEmpty(collectionName))
            {
                WriteSQL("[" + Database.ConnName + "]GetSchema");
                dt = conn.GetSchema();
            }
            else
            {
                WriteSQL("[" + Database.ConnName + "]GetSchema(\"" + collectionName + "\")");
                dt = conn.GetSchema(collectionName);
            }
        }
        else
        {
            var sb = new StringBuilder();
            foreach (var item in restrictionValues)
            {
                sb.Append(", ");
                if (item == null)
                    sb.Append("null");
                else
                    sb.AppendFormat("\"{0}\"", item);
            }
            WriteSQL("[" + Database.ConnName + "]GetSchema(\"" + collectionName + "\"" + sb + ")");
            dt = conn.GetSchema(collectionName, restrictionValues);
        }

        sw.Stop();
        // 耗时超过多少秒输出错误日志
        if (sw.ElapsedMilliseconds > 1000) DAL.WriteLog("GetSchema耗时 {0:n0}ms", sw.ElapsedMilliseconds);

        return dt;
    }
    #endregion

    #region Sql日志输出
    /// <summary>是否输出SQL语句，默认为XCode调试开关XCode.Debug</summary>
    public Boolean ShowSQL { get; set; }

    private static ILog logger;

    /// <summary>写入SQL到文本中</summary>
    /// <param name="sql"></param>
    public void WriteSQL(String sql)
    {
        if (sql.IsNullOrEmpty()) return;

        // 如果页面设定有XCode_SQLList列表，则往列表写入SQL语句
        DAL.LocalFilter?.Invoke(sql);

        if (!ShowSQL) return;

        var sqlpath = Setting.Current.SQLPath;
        if (String.IsNullOrEmpty(sqlpath))
            WriteLog(sql);
        else
        {
            logger ??= TextFileLog.Create(sqlpath);
            logger.Info(sql);
        }
    }

    private String GetSql(DbCommand cmd)
    {
        var max = (Database as DbBase).SQLMaxLength;
        try
        {
            var sql = cmd.CommandText;

            // 诊断信息
            /*if (XTrace.Log.Level <= LogLevel.Debug)*/
            sql = $"[{Database.ConnName}] {sql}";

            var ps = cmd.Parameters;
            if (ps != null && ps.Count > 0)
            {
                var sb = Pool.StringBuilder.Get();
                sb.Append(sql);
                sb.Append(" [");
                for (var i = 0; i < ps.Count; i++)
                {
                    if (i > 0) sb.Append(", ");
                    var v = ps[i].Value;
                    var sv = "";
                    if (v is Byte[])
                    {
                        var bv = v as Byte[];
                        if (bv.Length > 8)
                            sv = $"[{bv.Length}]0x{BitConverter.ToString(bv, 0, 8)}...";
                        else
                            sv = $"[{bv.Length}]0x{BitConverter.ToString(bv)}";
                    }
                    else if (v is String str && str.Length > 64)
                        sv = $"[{str.Length}]{str[..64]}...";
                    else
                        sv = v is DateTime dt ? dt.ToFullString() : (v + "");
                    sb.AppendFormat("{0}={1}", ps[i].ParameterName, sv);
                }
                sb.Append(']');
                sql = sb.Put(true);
            }

            // 截断超长字符串
            if (max > 0)
            {
                if (sql.Length > max && sql.StartsWithIgnoreCase("Insert")) sql = sql[..(max / 2)] + "..." + sql[^(max / 2)..];
            }

            return sql;
        }
        catch { return null; }
    }

    public String WriteSQL(DbCommand cmd)
    {
        // 如果页面设定有XCode_SQLList列表，则往列表写入SQL语句
        if (!ShowSQL && DAL.LocalFilter == null) return null;

        var sql = GetSql(cmd);

        WriteSQL(sql);

        return sql;
    }

    /// <summary>输出日志</summary>
    /// <param name="format"></param>
    /// <param name="args"></param>
    public static void WriteLog(String format, params Object[] args) => XTrace.WriteLine(format, args);
    #endregion

    #region SQL时间跟踪
    private Stopwatch _swSql;
    private static readonly HashSet<String> _trace_sqls = new(StringComparer.OrdinalIgnoreCase);

    protected void BeginTrace()
    {
        if ((Database as DbBase).TraceSQLTime <= 0) return;

        _swSql ??= new Stopwatch();

        if (_swSql.IsRunning) _swSql.Stop();

        _swSql.Reset();
        _swSql.Start();
    }

    protected void EndTrace(DbCommand cmd, String sql = null)
    {
        if (_swSql == null) return;

        _swSql.Stop();

        if (_swSql.ElapsedMilliseconds < (Database as DbBase).TraceSQLTime) return;

        if (sql.IsNullOrEmpty()) sql = GetSql(cmd);
        if (sql.IsNullOrEmpty()) return;

        // 同一个SQL只需要报警一次
        if (_trace_sqls.Contains(sql)) return;
        lock (_trace_sqls)
        {
            if (_trace_sqls.Contains(sql)) return;

            if (_trace_sqls.Count >= 1000) _trace_sqls.Clear();
            _trace_sqls.Add(sql);
        }

        XTrace.WriteLine("慢SQL[{0:n0}ms] {1}", _swSql.ElapsedMilliseconds, sql);
    }
    #endregion
}